DROP SEQUENCE SEQ_CUSTOMER_NO;
DROP SEQUENCE SEQ_ROUTE_NO;
DROP SEQUENCE SEQ_BILLING_NO;
DROP SEQUENCE SEQ_SUBSCRIPTION_NO;
DROP SEQUENCE SEQ_DELIVERY_NO;
DROP SEQUENCE SEQ_SUPPLIER_NO;
DROP SEQUENCE SEQ_PRODUCT_NO;
DROP SEQUENCE SEQ_DRIVER_NO;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE ROUTE CASCADE CONSTRAINTS;
DROP TABLE BILLING CASCADE CONSTRAINTS;
DROP TABLE SUBSCRIPTION CASCADE CONSTRAINTS;
DROP TABLE DELIVERY CASCADE CONSTRAINTS;
DROP TABLE SUPPLIER CASCADE CONSTRAINTS;
DROP TABLE PRODUCT CASCADE CONSTRAINTS;
DROP TABLE DRIVER CASCADE CONSTRAINTS;
DROP TABLE DRIVER_DELIVERY_CHANGE CASCADE CONSTRAINTS;
--CREATE SEQUENCES
CREATE SEQUENCE SEQ_DRIVER_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_DELIVERY_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_ROUTE_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_SUPPLIER_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_CUSTOMER_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_BILLING_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_SUBSCRIPTION_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
CREATE SEQUENCE SEQ_PRODUCT_NO MINVALUE 100 START WITH 100 INCREMENT BY 1 MAXVALUE 99999 CACHE 10;
  --CREATE TABLES
  --CREATE DRIVER TABLE
  CREATE TABLE DRIVER
    (
      DRIVER_NO            NUMBER(5),
      FIRST_NAME           VARCHAR(15) NOT NULL,
      LAST_NAME            VARCHAR(15) NOT NULL,
      CONTACT_NO           VARCHAR(15),
      ADDRESS              VARCHAR(30) NOT NULL,
      VEHICLE_REGISTRATION VARCHAR(15) DEFAULT 'NON ASSIGNED',
      CONSTRAINT PK_DRIVER_NO PRIMARY KEY (DRIVER_NO)
    );
  --CREATE DELIVERY TABLE
  CREATE TABLE DELIVERY
    (
      DELIVERY_NO NUMBER(5),
      DRIVER_NO   NUMBER(5),
      DELIVERY_DATE DATE,
      IS_DELIVERED CHAR DEFAULT '0' CHECK (IS_DELIVERED IN(0,1)),
      CONSTRAINT PK_DELIVERY_NO PRIMARY KEY (DELIVERY_NO),
      CONSTRAINT FK_DRIVER_NO FOREIGN KEY (DRIVER_NO) REFERENCES DRIVER( DRIVER_NO)
    );
  --CREATE ROUTE TABLE
  CREATE TABLE ROUTE
    (
      ROUTE_NO    NUMBER(5),
      DELIVERY_NO NUMBER(5),
      MAIN_ROUTE  VARCHAR(15),
      CONSTRAINT PK_ROUTE_NO PRIMARY KEY (ROUTE_NO),
      CONSTRAINT FK_DELIVERY_NO_ROUTE FOREIGN KEY (DELIVERY_NO) REFERENCES DELIVERY(DELIVERY_NO)
    );
  --CREATE SUPPLIER TABLE
  CREATE TABLE SUPPLIER
    (
      SUPPLIER_NO   NUMBER(5),
      SUPPLIER_NAME VARCHAR(15) NOT NULL,
      CONTACT_NO    VARCHAR(15) NOT NULL,
      ADDRESS       VARCHAR(30) NOT NULL,
      CONSTRAINT PK_SUPPLIER_NO PRIMARY KEY (SUPPLIER_NO)
    );
  --CREATE CUSTOMER TABLE
  CREATE TABLE CUSTOMER
    (
      CUSTOMER_NO NUMBER(5),
      FIRST_NAME  VARCHAR(15) NOT NULL,
      LAST_NAME   VARCHAR(15) NOT NULL,
      CONTACT_NO  VARCHAR(15),
      ADDRESS     VARCHAR(30) NOT NULL,
      CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
    );
  --CREATE BILLING TABLE
  CREATE TABLE BILLING
    (
      BILLING_NO      NUMBER(5),
      BILLING_ADDRESS VARCHAR(30),
      BILLING_AMOUNT  NUMBER(5,2),
      CONSTRAINT PK_BILLING_NO PRIMARY KEY (BILLING_NO)
    );
  --CREATE SUBSCRIPTION TABLE
  CREATE TABLE SUBSCRIPTION
    (
      SUBSCRIPTION_NO NUMBER(5),
      CUSTOMER_NO     NUMBER(5),
      DELIVERY_NO     NUMBER(5),
      BILLING_NO      NUMBER(5),
      SUBSCRIPTION_DATE DATE DEFAULT SYSDATE,
      BASE_AMOUNT  NUMBER(5,2) NOT NULL,
      TOTAL_AMOUNT NUMBER(5,2),
      CONSTRAINT PK_SUBSCRIPTION_NO PRIMARY KEY (SUBSCRIPTION_NO),
      CONSTRAINT FK_CUSTOMER_NO FOREIGN KEY (CUSTOMER_NO) REFERENCES CUSTOMER( CUSTOMER_NO),
      CONSTRAINT FK_DELIVERY_NO_SUBSCRIPTION FOREIGN KEY (DELIVERY_NO) REFERENCES DELIVERY(DELIVERY_NO),
      CONSTRAINT FK_BILLING_NO FOREIGN KEY (BILLING_NO) REFERENCES BILLING( BILLING_NO)
    );
  --CREATE PRODUCT TABLE
  CREATE TABLE PRODUCT
    (
      PRODUCT_NO          NUMBER(5),
      SUBSCRIPTION_NO     NUMBER(5),
      SUPPLIER_NO         NUMBER(5),
      PRODUCT_PRICE       NUMBER(5,2) NOT NULL,
      PRODUCT_DESCRIPTION VARCHAR(50),
      CONSTRAINT PK_PRODUCT_NO PRIMARY KEY (PRODUCT_NO),
      CONSTRAINT FK_SUBSCRIPTION_NO FOREIGN KEY (SUBSCRIPTION_NO) REFERENCES SUBSCRIPTION(SUBSCRIPTION_NO),
      CONSTRAINT FK_SUPPLIER_NO FOREIGN KEY (SUPPLIER_NO) REFERENCES SUPPLIER( SUPPLIER_NO)
    );
  --TRIGGER TABLES
  CREATE TABLE DRIVER_DELIVERY_CHANGE
    (
      DELIVERY_NO   NUMBER(5),
      USER_ID       VARCHAR2(30),
      OLD_DRIVER_NO NUMBER(5),
      NEW_DRIVER_NO NUMBER(5),
      CHANGED_DATE DATE DEFAULT SYSDATE,
      CONSTRAINT PKEY_DELIVERY_NO PRIMARY KEY (DELIVERY_NO)
    );
  --TRIGGERS
CREATE OR REPLACE TRIGGER DELIVERY_CHANGE BEFORE
  UPDATE OF DRIVER_NO, DELIVERY_NO ON DELIVERY FOR EACH ROW BEGIN
  INSERT
  INTO DRIVER_DELIVERY_CHANGE
    (
      DELIVERY_NO,
      USER_ID,
      OLD_DRIVER_NO,
      NEW_DRIVER_NO,
      CHANGED_DATE
    )
    VALUES
    (
      :OLD.DELIVERY_NO,
      USER,
      :OLD.DRIVER_NO,
      :NEW.DRIVER_NO,
      SYSDATE
    );
END;
/
--CREATE PACKAGES
CREATE OR REPLACE
PACKAGE TYPES
AS
TYPE REF_CURSOR
IS
  REF
  CURSOR;
  END TYPES;
  /
  --CREATE ADD_DATA PACKAGE
CREATE OR REPLACE
PACKAGE ADD_DATA
AS
PROCEDURE BILLING
  (
    P_BILLING_ADDRESS VARCHAR2,
    P_BILLING_AMOUNT  NUMBER
  )
  ;
PROCEDURE CUSTOMER
  (
    P_FIRST_NAME VARCHAR2,
    P_LAST_NAME  VARCHAR2,
    P_CONTACT_NO VARCHAR2,
    P_ADDRESS    VARCHAR2
  )
  ;
PROCEDURE DELIVERY
  (
    P_DRIVER_NO NUMBER,
    P_DELIVERY_DATE DATE,
    P_IS_DELIVERED CHAR
  )
  ;
PROCEDURE DRIVER
  (
    P_FIRST_NAME           VARCHAR2,
    P_LAST_NAME            VARCHAR2,
    P_CONTACT_NO           VARCHAR2,
    P_ADDRESS              VARCHAR2,
    P_VEHICLE_REGISTRATION VARCHAR2
  )
  ;
PROCEDURE PRODUCT
  (
    P_SUBSCRIPTION_NO     NUMBER,
    P_SUPPLIER_NO         NUMBER,
    P_PRODUCT_PRICE       NUMBER,
    P_PRODUCT_DESCRIPTION VARCHAR2
  )
  ;
PROCEDURE ROUTE
  (
    P_DELIVERY_NO NUMBER,
    P_MAIN_ROUTE  VARCHAR2
  )
  ;
PROCEDURE SUBSCRIPTION
  (
    P_CUSTOMER_NO NUMBER,
    P_DELIVERY_NO NUMBER,
    P_BILLING_NO  NUMBER,
    P_SUBSCRIPTION_DATE DATE,
    P_BASE_AMOUNT  NUMBER,
    P_TOTAL_AMOUNT NUMBER
  )
  ;
PROCEDURE SUPPLIER
  (
    P_SUPPLIER_NAME VARCHAR2,
    P_CONTACT_NO    VARCHAR2,
    P_ADDRESS       VARCHAR2
  )
  ;
END ADD_DATA;
/
CREATE OR REPLACE
PACKAGE BODY ADD_DATA
AS
PROCEDURE BILLING
  (
    P_BILLING_ADDRESS VARCHAR2,
    P_BILLING_AMOUNT  NUMBER
  )
IS
BEGIN
  INSERT
  INTO BILLING VALUES
    (
      SEQ_BILLING_NO.NEXTVAL,
      P_BILLING_ADDRESS,
      P_BILLING_AMOUNT
    );
END BILLING;
PROCEDURE CUSTOMER
  (
    P_FIRST_NAME VARCHAR2,
    P_LAST_NAME  VARCHAR2,
    P_CONTACT_NO VARCHAR2,
    P_ADDRESS    VARCHAR2
  )
IS
BEGIN
  INSERT
  INTO CUSTOMER VALUES
    (
      SEQ_CUSTOMER_NO.NEXTVAL,
      P_FIRST_NAME,
      P_LAST_NAME,
      P_CONTACT_NO,
      P_ADDRESS
    );
END CUSTOMER;
PROCEDURE DELIVERY
  (
    P_DRIVER_NO NUMBER,
    P_DELIVERY_DATE DATE,
    P_IS_DELIVERED CHAR
  )
IS
BEGIN
  INSERT
  INTO DELIVERY VALUES
    (
      SEQ_DELIVERY_NO.NEXTVAL,
      P_DRIVER_NO,
      P_DELIVERY_DATE,
      P_IS_DELIVERED
    );
END DELIVERY;
PROCEDURE DRIVER
  (
    P_FIRST_NAME           VARCHAR2,
    P_LAST_NAME            VARCHAR2,
    P_CONTACT_NO           VARCHAR2,
    P_ADDRESS              VARCHAR2,
    P_VEHICLE_REGISTRATION VARCHAR2
  )
IS
BEGIN
  INSERT
  INTO DRIVER VALUES
    (
      SEQ_DRIVER_NO.NEXTVAL,
      P_FIRST_NAME,
      P_LAST_NAME,
      P_CONTACT_NO,
      P_ADDRESS,
      P_VEHICLE_REGISTRATION
    );
END DRIVER;
PROCEDURE PRODUCT
  (
    P_SUBSCRIPTION_NO     NUMBER,
    P_SUPPLIER_NO         NUMBER,
    P_PRODUCT_PRICE       NUMBER,
    P_PRODUCT_DESCRIPTION VARCHAR2
  )
IS
BEGIN
  INSERT
  INTO PRODUCT VALUES
    (
      SEQ_PRODUCT_NO.NEXTVAL,
      P_SUBSCRIPTION_NO,
      P_SUPPLIER_NO,
      P_PRODUCT_PRICE,
      P_PRODUCT_DESCRIPTION
    );
END PRODUCT;
PROCEDURE ROUTE
  (
    P_DELIVERY_NO NUMBER,
    P_MAIN_ROUTE  VARCHAR2
  )
IS
BEGIN
  INSERT
  INTO ROUTE VALUES
    (
      SEQ_ROUTE_NO.NEXTVAL,
      P_DELIVERY_NO,
      P_MAIN_ROUTE
    );
END ROUTE;
PROCEDURE SUBSCRIPTION
  (
    P_CUSTOMER_NO NUMBER,
    P_DELIVERY_NO NUMBER,
    P_BILLING_NO  NUMBER,
    P_SUBSCRIPTION_DATE DATE,
    P_BASE_AMOUNT  NUMBER,
    P_TOTAL_AMOUNT NUMBER
  )
IS
BEGIN
  INSERT
  INTO SUBSCRIPTION VALUES
    (
      SEQ_SUBSCRIPTION_NO.NEXTVAL,
      P_CUSTOMER_NO,
      P_DELIVERY_NO,
      P_BILLING_NO,
      P_SUBSCRIPTION_DATE,
      P_BASE_AMOUNT,
      P_TOTAL_AMOUNT
    );
END SUBSCRIPTION;
PROCEDURE SUPPLIER
  (
    P_SUPPLIER_NAME VARCHAR2,
    P_CONTACT_NO    VARCHAR2,
    P_ADDRESS       VARCHAR2
  )
IS
BEGIN
  INSERT
  INTO SUPPLIER VALUES
    (
      SEQ_SUPPLIER_NO.NEXTVAL,
      P_SUPPLIER_NAME,
      P_CONTACT_NO,
      P_ADDRESS
    );
END SUPPLIER;
END ADD_DATA;
/
--CREATE UPDATE_DATA PACKAGE
CREATE OR REPLACE
PACKAGE UPDATE_DATA
AS
PROCEDURE BILLING
  (
    P_BILLING_NO      NUMBER,
    P_BILLING_ADDRESS VARCHAR2,
    P_BILLING_AMOUNT  NUMBER
  )
  ;
PROCEDURE CUSTOMER
  (
    P_CUSTOMER_NO NUMBER,
    P_FIRST_NAME  VARCHAR2,
    P_LAST_NAME   VARCHAR2,
    P_CONTACT_NO  VARCHAR2,
    P_ADDRESS     VARCHAR2
  )
  ;
PROCEDURE DELIVERY
  (
    P_DELIVERY_NO NUMBER,
    P_DRIVER_NO   NUMBER,
    P_DELIVERY_DATE DATE,
    P_IS_DELIVERED CHAR
  )
  ;
PROCEDURE DRIVER
  (
    P_DRIVER_NO            NUMBER,
    P_FIRST_NAME           VARCHAR2,
    P_LAST_NAME            VARCHAR2,
    P_CONTACT_NO           VARCHAR2,
    P_ADDRESS              VARCHAR2,
    P_VEHICLE_REGISTRATION VARCHAR2
  )
  ;
PROCEDURE PRODUCT
  (
    P_PRODUCT_NO          NUMBER,
    P_SUBSCRIPTION_NO     NUMBER,
    P_SUPPLIER_NO         NUMBER,
    P_PRODUCT_PRICE       NUMBER,
    P_PRODUCT_DESCRIPTION VARCHAR2
  )
  ;
PROCEDURE ROUTE
  (
    P_ROUTE_NO    NUMBER,
    P_DELIVERY_NO NUMBER,
    P_MAIN_ROUTE  VARCHAR2
  )
  ;
PROCEDURE SUBSCRIPTION
  (
    P_SUBSCRIPTION_NO NUMBER,
    P_CUSTOMER_NO     NUMBER,
    P_DELIVERY_NO     NUMBER,
    P_BILLING_NO      NUMBER,
    P_SUBSCRIPTION_DATE DATE,
    P_BASE_AMOUNT  NUMBER,
    P_TOTAL_AMOUNT NUMBER
  )
  ;
PROCEDURE SUPPLIER
  (
    P_SUPPLIER_NO   NUMBER,
    P_SUPPLIER_NAME VARCHAR2,
    P_CONTACT_NO    VARCHAR2,
    P_ADDRESS       VARCHAR2
  )
  ;
END UPDATE_DATA;
/
CREATE OR REPLACE
PACKAGE BODY UPDATE_DATA
AS
PROCEDURE BILLING
  (
    P_BILLING_NO      NUMBER,
    P_BILLING_ADDRESS VARCHAR2,
    P_BILLING_AMOUNT  NUMBER
  )
IS
BEGIN
  UPDATE BILLING
  SET BILLING_ADDRESS = P_BILLING_ADDRESS,
    BILLING_AMOUNT    = P_BILLING_AMOUNT
  WHERE BILLING_NO    = P_BILLING_NO;
END BILLING;
PROCEDURE CUSTOMER(
    P_CUSTOMER_NO NUMBER,
    P_FIRST_NAME  VARCHAR2,
    P_LAST_NAME   VARCHAR2,
    P_CONTACT_NO  VARCHAR2,
    P_ADDRESS     VARCHAR2)
IS
BEGIN
  UPDATE CUSTOMER
  SET FIRST_NAME    = P_FIRST_NAME,
    LAST_NAME       = P_LAST_NAME,
    CONTACT_NO      = P_CONTACT_NO,
    ADDRESS         = P_ADDRESS
  WHERE CUSTOMER_NO = P_CUSTOMER_NO;
END CUSTOMER;
PROCEDURE DELIVERY(
    P_DELIVERY_NO NUMBER,
    P_DRIVER_NO   NUMBER,
    P_DELIVERY_DATE DATE,
    P_IS_DELIVERED CHAR)
IS
BEGIN
  UPDATE DELIVERY
  SET DRIVER_NO     = P_DRIVER_NO,
    DELIVERY_DATE   = P_DELIVERY_DATE,
    IS_DELIVERED    = P_IS_DELIVERED
  WHERE DELIVERY_NO = P_DELIVERY_NO;
END DELIVERY;
PROCEDURE DRIVER(
    P_DRIVER_NO            NUMBER,
    P_FIRST_NAME           VARCHAR2,
    P_LAST_NAME            VARCHAR2,
    P_CONTACT_NO           VARCHAR2,
    P_ADDRESS              VARCHAR2,
    P_VEHICLE_REGISTRATION VARCHAR2)
IS
BEGIN
  UPDATE DRIVER
  SET FIRST_NAME         = P_FIRST_NAME,
    LAST_NAME            = P_LAST_NAME,
    CONTACT_NO           = P_CONTACT_NO,
    ADDRESS              = P_ADDRESS,
    VEHICLE_REGISTRATION = P_VEHICLE_REGISTRATION
  WHERE DRIVER_NO        = P_DRIVER_NO ;
END DRIVER;
PROCEDURE PRODUCT(
    P_PRODUCT_NO          NUMBER,
    P_SUBSCRIPTION_NO     NUMBER,
    P_SUPPLIER_NO         NUMBER,
    P_PRODUCT_PRICE       NUMBER,
    P_PRODUCT_DESCRIPTION VARCHAR2)
IS
BEGIN
  UPDATE PRODUCT
  SET SUBSCRIPTION_NO   = P_SUBSCRIPTION_NO,
    SUPPLIER_NO         = P_SUPPLIER_NO,
    PRODUCT_PRICE       = P_PRODUCT_PRICE,
    PRODUCT_DESCRIPTION = P_PRODUCT_DESCRIPTION
  WHERE PRODUCT_NO      = P_PRODUCT_NO;
END PRODUCT;
PROCEDURE ROUTE(
    P_ROUTE_NO    NUMBER,
    P_DELIVERY_NO NUMBER,
    P_MAIN_ROUTE  VARCHAR2)
IS
BEGIN
  UPDATE ROUTE
  SET DELIVERY_NO = P_DELIVERY_NO,
    MAIN_ROUTE    = P_MAIN_ROUTE
  WHERE ROUTE_NO  = P_ROUTE_NO;
END ROUTE;
PROCEDURE SUBSCRIPTION(
    P_SUBSCRIPTION_NO NUMBER,
    P_CUSTOMER_NO     NUMBER,
    P_DELIVERY_NO     NUMBER,
    P_BILLING_NO      NUMBER,
    P_SUBSCRIPTION_DATE DATE,
    P_BASE_AMOUNT  NUMBER,
    P_TOTAL_AMOUNT NUMBER)
IS
BEGIN
  UPDATE SUBSCRIPTION
  SET CUSTOMER_NO       = P_CUSTOMER_NO,
    DELIVERY_NO         = P_DELIVERY_NO,
    BILLING_NO          = P_BILLING_NO,
    SUBSCRIPTION_DATE   = P_SUBSCRIPTION_DATE,
    BASE_AMOUNT         = P_BASE_AMOUNT,
    TOTAL_AMOUNT        = P_TOTAL_AMOUNT
  WHERE SUBSCRIPTION_NO = P_SUBSCRIPTION_NO;
END SUBSCRIPTION;
PROCEDURE SUPPLIER(
    P_SUPPLIER_NO   NUMBER,
    P_SUPPLIER_NAME VARCHAR2,
    P_CONTACT_NO    VARCHAR2,
    P_ADDRESS       VARCHAR2)
IS
BEGIN
  UPDATE SUPPLIER
  SET SUPPLIER_NAME = P_SUPPLIER_NAME,
    CONTACT_NO      = P_CONTACT_NO,
    ADDRESS         = P_ADDRESS
  WHERE SUPPLIER_NO = P_SUPPLIER_NO;
END SUPPLIER;
END UPDATE_DATA;
/
--CREATE DELETE_DATA PACKAGE
CREATE OR REPLACE
PACKAGE DELETE_DATA
AS
PROCEDURE BILLING(
    P_BILLING_NO NUMBER);
PROCEDURE CUSTOMER(
    P_CUSTOMER_NO NUMBER);
PROCEDURE DELIVERY(
    P_DELIVERY_NO NUMBER);
PROCEDURE DRIVER(
    P_DRIVER_NO NUMBER);
PROCEDURE PRODUCT(
    P_PRODUCT_NO NUMBER);
PROCEDURE ROUTE(
    P_ROUTE_NO NUMBER);
PROCEDURE SUBSCRIPTION(
    P_SUBSCRIPTION_NO NUMBER);
PROCEDURE SUPPLIER(
    P_SUPPLIER_NO NUMBER);
END DELETE_DATA;
/
CREATE OR REPLACE
PACKAGE BODY DELETE_DATA
AS
PROCEDURE BILLING(
    P_BILLING_NO NUMBER)
IS
BEGIN
  DELETE FROM BILLING WHERE BILLING_NO = P_BILLING_NO;
END BILLING;
PROCEDURE CUSTOMER(
    P_CUSTOMER_NO NUMBER)
IS
BEGIN
  DELETE FROM CUSTOMER WHERE CUSTOMER_NO = P_CUSTOMER_NO;
END CUSTOMER;
PROCEDURE DELIVERY(
    P_DELIVERY_NO NUMBER)
IS
BEGIN
  DELETE FROM DELIVERY WHERE DELIVERY_NO = P_DELIVERY_NO;
END DELIVERY;
PROCEDURE DRIVER(
    P_DRIVER_NO NUMBER)
IS
BEGIN
  DELETE FROM DRIVER WHERE DRIVER_NO = P_DRIVER_NO;
END DRIVER;
PROCEDURE PRODUCT(
    P_PRODUCT_NO NUMBER)
IS
BEGIN
  DELETE FROM PRODUCT WHERE PRODUCT_NO = P_PRODUCT_NO;
END PRODUCT;
PROCEDURE ROUTE(
    P_ROUTE_NO NUMBER)
IS
BEGIN
  DELETE FROM ROUTE WHERE ROUTE_NO = P_ROUTE_NO;
END ROUTE;
PROCEDURE SUBSCRIPTION(
    P_SUBSCRIPTION_NO NUMBER)
IS
BEGIN
  DELETE FROM SUBSCRIPTION WHERE SUBSCRIPTION_NO = P_SUBSCRIPTION_NO;
END SUBSCRIPTION;
PROCEDURE SUPPLIER(
    P_SUPPLIER_NO NUMBER)
IS
BEGIN
  DELETE FROM SUPPLIER WHERE SUPPLIER_NO = P_SUPPLIER_NO;
END SUPPLIER;
END DELETE_DATA;
/
CREATE OR REPLACE
PACKAGE GET_DATA
AS
FUNCTION BILLING(
    P_BILLING_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION BILLINGS
  RETURN TYPES.REF_CURSOR;
FUNCTION CUSTOMER(
    P_CUSTOMER_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION CUSTOMERS
  RETURN TYPES.REF_CURSOR;
FUNCTION DELIVERY(
    P_DELIVERY_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION DELIVERIES
  RETURN TYPES.REF_CURSOR;
FUNCTION DRIVER(
    P_DRIVER_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION DRIVERS
  RETURN TYPES.REF_CURSOR;
FUNCTION PRODUCT(
    P_PRODUCT_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION PRODUCTS
  RETURN TYPES.REF_CURSOR;
FUNCTION ROUTE(
    P_ROUTE_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION ROUTES
  RETURN TYPES.REF_CURSOR;
FUNCTION SUBSCRIPTION(
    P_SUBSCRIPTION_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION SUBSCRIPTIONS
  RETURN TYPES.REF_CURSOR;
FUNCTION SUPPLIER(
    P_SUPPLIER_NO NUMBER)
  RETURN TYPES.REF_CURSOR;
FUNCTION SUPPLIERS
  RETURN TYPES.REF_CURSOR;
END GET_DATA;
/
--CREATE GET_DATA PACKAGE
CREATE OR REPLACE
PACKAGE BODY GET_DATA
AS
FUNCTION BILLING(
    P_BILLING_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  BILLING_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN BILLING_CURSOR FOR SELECT BILLING_NO,
  BILLING_ADDRESS,
  BILLING_AMOUNT FROM BILLING WHERE BILLING_NO = P_BILLING_NO;
  RETURN BILLING_CURSOR;
END BILLING;
FUNCTION BILLINGS
  RETURN TYPES.REF_CURSOR
IS
  BILLING_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN BILLING_CURSOR FOR SELECT BILLING_NO,
  BILLING_ADDRESS,
  BILLING_AMOUNT FROM BILLING;
  RETURN BILLING_CURSOR;
END BILLINGS;
FUNCTION CUSTOMER(
    P_CUSTOMER_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  CUSTOMER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN CUSTOMER_CURSOR FOR SELECT CUSTOMER_NO,
  FIRST_NAME,
  LAST_NAME,
  CONTACT_NO,
  ADDRESS FROM CUSTOMER WHERE CUSTOMER_NO = P_CUSTOMER_NO;
  RETURN CUSTOMER_CURSOR;
END CUSTOMER;
FUNCTION CUSTOMERS
  RETURN TYPES.REF_CURSOR
IS
  CUSTOMER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN CUSTOMER_CURSOR FOR SELECT CUSTOMER_NO,
  FIRST_NAME,
  LAST_NAME,
  CONTACT_NO,
  ADDRESS FROM CUSTOMER;
  RETURN CUSTOMER_CURSOR;
END CUSTOMERS;
FUNCTION DELIVERY(
    P_DELIVERY_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  DELIVERY_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN DELIVERY_CURSOR FOR SELECT DELIVERY_NO,
  DRIVER_NO,
  DELIVERY_DATE,
  IS_DELIVERED FROM DELIVERY WHERE DELIVERY_NO = P_DELIVERY_NO;
  RETURN DELIVERY_CURSOR;
END DELIVERY;
FUNCTION DELIVERIES
  RETURN TYPES.REF_CURSOR
IS
  DELIVERY_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN DELIVERY_CURSOR FOR SELECT DELIVERY_NO,
  DRIVER_NO,
  DELIVERY_DATE,
  IS_DELIVERED FROM DELIVERY;
  RETURN DELIVERY_CURSOR;
END DELIVERIES;
FUNCTION DRIVER(
    P_DRIVER_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  DRIVER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN DRIVER_CURSOR FOR SELECT DRIVER_NO,
  FIRST_NAME,
  LAST_NAME,
  CONTACT_NO,
  ADDRESS,
  VEHICLE_REGISTRATION FROM DRIVER WHERE DRIVER_NO = P_DRIVER_NO;
  RETURN DRIVER_CURSOR;
END DRIVER;
FUNCTION DRIVERS
  RETURN TYPES.REF_CURSOR
IS
  DRIVER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN DRIVER_CURSOR FOR SELECT DRIVER_NO,
  FIRST_NAME,
  LAST_NAME,
  CONTACT_NO,
  ADDRESS,
  VEHICLE_REGISTRATION FROM DRIVER;
  RETURN DRIVER_CURSOR;
END DRIVERS;
FUNCTION PRODUCT(
    P_PRODUCT_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  PRODUCT_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN PRODUCT_CURSOR FOR SELECT PRODUCT_NO,
  SUBSCRIPTION_NO,
  SUPPLIER_NO,
  PRODUCT_PRICE,
  PRODUCT_DESCRIPTION FROM PRODUCT WHERE PRODUCT_NO = P_PRODUCT_NO;
  RETURN PRODUCT_CURSOR;
END PRODUCT;
FUNCTION PRODUCTS
  RETURN TYPES.REF_CURSOR
IS
  PRODUCT_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN PRODUCT_CURSOR FOR SELECT PRODUCT_NO,
  SUBSCRIPTION_NO,
  SUPPLIER_NO,
  PRODUCT_PRICE,
  PRODUCT_DESCRIPTION FROM PRODUCT;
  RETURN PRODUCT_CURSOR;
END PRODUCTS;
FUNCTION ROUTE(
    P_ROUTE_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  ROUTE_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN ROUTE_CURSOR FOR SELECT ROUTE_NO,
  DELIVERY_NO,
  MAIN_ROUTE FROM ROUTE WHERE ROUTE_NO = P_ROUTE_NO;
  RETURN ROUTE_CURSOR;
END ROUTE;
FUNCTION ROUTES
  RETURN TYPES.REF_CURSOR
IS
  ROUTE_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN ROUTE_CURSOR FOR SELECT ROUTE_NO,
  DELIVERY_NO,
  MAIN_ROUTE FROM ROUTE;
  RETURN ROUTE_CURSOR;
END ROUTES;
FUNCTION SUBSCRIPTION(
    P_SUBSCRIPTION_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  SUBSCRIPTION_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN SUBSCRIPTION_CURSOR FOR SELECT SUBSCRIPTION_NO,
  CUSTOMER_NO,
  DELIVERY_NO,
  BILLING_NO,
  SUBSCRIPTION_DATE,
  BASE_AMOUNT,
  TOTAL_AMOUNT FROM SUBSCRIPTION WHERE SUBSCRIPTION_NO = P_SUBSCRIPTION_NO;
  RETURN SUBSCRIPTION_CURSOR;
END SUBSCRIPTION;
FUNCTION SUBSCRIPTIONS
  RETURN TYPES.REF_CURSOR
IS
  SUBSCRIPTION_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN SUBSCRIPTION_CURSOR FOR SELECT SUBSCRIPTION_NO,
  CUSTOMER_NO,
  DELIVERY_NO,
  BILLING_NO,
  SUBSCRIPTION_DATE,
  BASE_AMOUNT,
  TOTAL_AMOUNT FROM SUBSCRIPTION;
  RETURN SUBSCRIPTION_CURSOR;
END SUBSCRIPTIONS;
FUNCTION SUPPLIER(
    P_SUPPLIER_NO NUMBER)
  RETURN TYPES.REF_CURSOR
IS
  SUPPLIER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN SUPPLIER_CURSOR FOR SELECT SUPPLIER_NO,
  SUPPLIER_NAME,
  CONTACT_NO,
  ADDRESS FROM SUPPLIER WHERE SUPPLIER_NO = P_SUPPLIER_NO;
  RETURN SUPPLIER_CURSOR;
END SUPPLIER;
FUNCTION SUPPLIERS
  RETURN TYPES.REF_CURSOR
IS
  SUPPLIER_CURSOR TYPES.REF_CURSOR;
BEGIN
  OPEN SUPPLIER_CURSOR FOR SELECT SUPPLIER_NO,
  SUPPLIER_NAME,
  CONTACT_NO,
  ADDRESS FROM SUPPLIER;
  RETURN SUPPLIER_CURSOR;
END SUPPLIERS;
END GET_DATA;
/
--Insert Data to Driver
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Lance',
    'Reid',
    '0731129035',
    '4 rolyny court',
    'CA33455'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Shane',
    'Kyle',
    '0648526475',
    '12 Hak rd',
    'CA44756'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Kim',
    'Simon',
    '08126554457',
    '18 vlee rd',
    'CA34455'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Thomas',
    'Jackson',
    '07994832722',
    '334 main rd',
    'CA33455'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Sam',
    'Nike',
    '0712689493',
    '9 Hydra rd',
    'CA44756'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'David',
    'de Waal',
    '0734745893',
    '11 deens st',
    'CA10122'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Patrick',
    'Howarth',
    '0845509224',
    '55 north rd',
    'CA33455'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Mick',
    'Al',
    '0764908834',
    '43 Tan st',
    'CA33455'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Mike',
    'Lee',
    '0812266755',
    '886 Sea Flats',
    'CA10122'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Jack',
    'Egle',
    '0731129035',
    '8th Lane no 4',
    'CA99654'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Dwyne',
    'Miles',
    '0747793236',
    'Main st west',
    'CA99654'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Andrew',
    'Hather',
    '0770485923',
    '9th St west',
    'CA90933'
  );
INSERT
INTO DRIVER VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Ruan',
    'Moller',
    '0731245998',
    '5, Open way',
    'CA17174'
  );
INSERT
INTO DRIVER
  (
    DRIVER_NO,
    FIRST_NAME,
    LAST_NAME,
    CONTACT_NO,
    ADDRESS
  )
  VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Hugo',
    'Boss',
    '0784848832',
    '990 Taken st'
  );
INSERT
INTO DRIVER
  (
    DRIVER_NO,
    FIRST_NAME,
    LAST_NAME,
    CONTACT_NO,
    ADDRESS
  )
  VALUES
  (
    SEQ_DRIVER_NO.nextval,
    'Phil',
    'Shakes',
    '0788994523',
    '49 Welkom rd'
  );
--Insert data to delivery table
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    100,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    100,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    100,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    100,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    101,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    101,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    101,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    102,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    102,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    103,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    103,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    104,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    104,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    104,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    105,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    105,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    105,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    105,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    106,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    106,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    107,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    108,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    108,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    108,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '1'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    109,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    110,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    110,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    110,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    111,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    111,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    111,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    112,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    112,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
INSERT
INTO DELIVERY VALUES
  (
    SEQ_DELIVERY_NO.NEXTVAL,
    112,
    TO_DATE('01/06/2011', 'DD/MM/YYYY'),
    '0'
  );
--Insert data into route
INSERT
INTO ROUTE VALUES
  (
    SEQ_ROUTE_NO.nextval,
    100,
    'M5'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 100, 'M5'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 102, 'M3'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 102, 'M3'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 103, 'M3'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 104, 'M3'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 104, 'M3'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 105, 'N1'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 106, 'N1'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 106, 'N1'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 107, 'N1'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 107, 'N1'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 108, 'N2'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 108, 'N2'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 109, 'N2'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 109, 'N2'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 110, 'N7'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 111, 'N7'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 112, 'N7'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 112, 'N7'
  );
INSERT INTO ROUTE VALUES
  ( SEQ_ROUTE_NO.nextval, 113, 'N7'
  );
--Insert data into customer
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Sandy',
    'Powan',
    '0743328514',
    '257 3rd Avenue'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Laim',
    'Powan',
    '074124514',
    '257 3rd Avenue'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Nicole',
    'Richards',
    '0832496347',
    '4 Stanley Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Byron',
    'Southgate',
    '0834127956',
    '56 Riedbok Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Donrique',
    'Bartes',
    '0841237635',
    '12 1st Avenue'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Jessie',
    'Gedult',
    '0827963354',
    '145 Stephen Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Cameron',
    'Smalls',
    '0827667895',
    '56 Duiker Avenue'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Jeremy',
    'Johnson',
    '0843359946',
    '89 Rainbow Avenue'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Gloria',
    'Marks',
    '0814453332',
    '964 Bree Street'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Jody',
    'Benjemin',
    '0825896345',
    '256 Broad Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Tarah',
    'Terez',
    '0835789624',
    '77 Heath Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Kyle',
    'Mering',
    '0842365112',
    '66 Devon Place'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Jessi',
    'Malan',
    '0833256648',
    '123 Shep Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Jennie',
    'Jacobs',
    '0814859654',
    '47 Creed Street'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Keanan',
    'George',
    '0833621455',
    '36 Stephen Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Joshua',
    'Jonson',
    '0823145273',
    '33 River Way'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Mary',
    'Alice',
    '0822365148',
    '15 Raymond Circle'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Courtney',
    'Winkworth',
    '0842632255',
    '82 2nd Ave'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Kelly',
    'Libber',
    '0827964489',
    '555 Klip Road'
  );
INSERT
INTO CUSTOMER VALUES
  (
    SEQ_CUSTOMER_NO.nextval,
    'Liam',
    'Reds',
    '0733048401',
    '156 Polar Road'
  );
--Insert Data to Billing
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '4 Stephen Place',
    252.36
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '7 Rainbow Street',
    128.52
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '765 Sheet Street',
    89.57
  );
INSERT INTO BILLING VALUES
  ( SEQ_BILLING_NO.nextval, '475 3rd Ave', 285.66
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '3 Stanley Road',
    122.22
  );
INSERT INTO BILLING VALUES
  ( SEQ_BILLING_NO.nextval, '67 Buck Road', 427.15
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '90 Reid Street',
    60.78
  );
INSERT INTO BILLING VALUES
  ( SEQ_BILLING_NO.nextval, '55 Dove Road', 166.32
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '77 Waterford Ave',
    322.54
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '17 Goya Street',
    57.90
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '127 Tulip Close',
    155.63
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '47 Linda Road',
    269.85
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '13 Stella Road',
    389.46
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '99 Phillip Road',
    158.90
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '12 Raymond Circle',
    512.34
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '1 Devon Place',
    123.23
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '65  Rosewood Street',
    222.68
  );
INSERT INTO BILLING VALUES
  ( SEQ_BILLING_NO.nextval, '147 4th Ave', 155.63
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '69 Whiteheart Lane',
    335.96
  );
INSERT
INTO BILLING VALUES
  (
    SEQ_BILLING_NO.nextval,
    '21 Jump Street',
    87.62
  );
--Insert data into SUPPLIER
INSERT
INTO SUPPLIER
  (
    SUPPLIER_NO,
    SUPPLIER_NAME,
    CONTACT_NO,
    ADDRESS
  )
  VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Jiffy Magazines',
    '0217583444',
    '123 Maitland Industry'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Bride Magazine',
    '0217053484',
    '12 Parrow Main rd'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'ELLE',
    '0217583444',
    '351 Pardon Eiland'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'The Gardener',
    '0217062651',
    '236 Long Street'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Destiny Man',
    '0217048256',
    '231 Elm Street'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Compleat Golfer',
    '0217555698',
    '478 Denton Street'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Fitness Mag',
    '02170421896',
    '23 Parin Road'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'CAR',
    '02154298745',
    '35 Duiker Street'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Muscle Evo',
    '0213214598',
    '89 Calrendon Street'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'SA Tuin',
    '0214563215',
    '122 Grove Road '
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Longevity',
    '0217062485',
    '245 Smith Road'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'PopularMechanic',
    '0217053214',
    '45 Hewwet Ave'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'SA Home Owner',
    '0217042185',
    ' 123 Techno Cresent'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'FHM',
    '0217702842',
    '357 Milnerton Drive'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'YOU',
    '0218521456',
    '456 Marconi Road'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Die Buger',
    '0218921146',
    '12 Rand Road'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Cape Argus',
    '0217896321',
    '14 Brentwood Park'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'Stuff',
    '0217512345',
    '166 Denne Road'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'WealthWise',
    '0214793245',
    '255 Protea Way'
  );
INSERT
INTO SUPPLIER VALUES
  (
    SEQ_SUPPLIER_NO.nextval,
    'PC Format',
    '0217062485',
    '25 Peperboom Cresent'
  );
--Insert data into SUBSCRIPTION
INSERT
INTO SUBSCRIPTION
  (
    SUBSCRIPTION_NO,
    CUSTOMER_NO,
    DELIVERY_NO,
    BILLING_NO,
    SUBSCRIPTION_DATE,
    BASE_AMOUNT,
    TOTAL_AMOUNT
  )
  VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    103,
    108,
    100,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    25.20,
    50.90
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    100,
    109,
    119,
    TO_DATE('29/04/2012', 'DD/MM/YYYY'),
    56.56,
    100.25
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    101,
    110,
    118,
    TO_DATE('01/04/2011', 'DD/MM/YYYY'),
    30.50,
    89.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    102,
    111,
    117,
    TO_DATE('25/02/2012', 'DD/MM/YYYY'),
    35.55,
    70.50
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    104,
    112,
    116,
    TO_DATE('15/06/2011', 'DD/MM/YYYY'),
    52.30,
    120.55
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    105,
    113,
    115,
    TO_DATE('01/10/2011', 'DD/MM/YYYY'),
    50.60,
    151.80
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    106,
    114,
    114,
    TO_DATE('15/07/2011', 'DD/MM/YYYY'),
    45.09,
    130.52
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    107,
    115,
    113,
    TO_DATE('30/05/2011', 'DD/MM/YYYY'),
    25.10,
    59.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    108,
    116,
    112,
    TO_DATE('30/06/2011', 'DD/MM/YYYY'),
    29.99,
    99.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    109,
    117,
    111,
    TO_DATE('30/07/2012', 'DD/MM/YYYY'),
    25.13,
    52.70
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    110,
    118,
    110,
    TO_DATE('30/01/2012', 'DD/MM/YYYY'),
    27.50,
    79.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    111,
    119,
    109,
    TO_DATE('30/05/2013', 'DD/MM/YYYY'),
    17.50,
    69.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    112,
    120,
    108,
    TO_DATE('15/11/2011', 'DD/MM/YYYY'),
    80.99,
    199.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    113,
    121,
    107,
    TO_DATE('31/12/2012', 'DD/MM/YYYY'),
    25.90,
    103.56
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    114,
    122,
    106,
    TO_DATE('30/09/2011', 'DD/MM/YYYY'),
    55.23,
    125.63
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    115,
    123,
    105,
    TO_DATE('01/01/2012', 'DD/MM/YYYY'),
    29.99,
    99.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    116,
    124,
    104,
    TO_DATE('30/03/2011', 'DD/MM/YYYY'),
    45.95,
    134.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    117,
    125,
    101,
    TO_DATE('25/07/2013', 'DD/MM/YYYY'),
    33.33,
    99.39
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    118,
    126,
    102,
    TO_DATE('31/07/2013', 'DD/MM/YYYY'),
    27.45,
    89.99
  );
INSERT
INTO SUBSCRIPTION VALUES
  (
    SEQ_SUBSCRIPTION_NO.nextval,
    119,
    127,
    103,
    TO_DATE('30/04/2012', 'DD/MM/YYYY'),
    36.50,
    152.63
  );
--Insert data into PRODUCT
INSERT
INTO PRODUCT
  (
    PRODUCT_NO,
    SUBSCRIPTION_NO,
    SUPPLIER_NO,
    PRODUCT_PRICE,
    PRODUCT_DESCRIPTION
  )
  VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    100,
    100,
    29.99,
    'DIY'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    101,
    101,
    31.50,
    'Weddings'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    102,
    102,
    17.50,
    'Fashion and Style'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    103,
    103,
    26.90,
    'Homes and Gardens'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    104,
    104,
    26.30,
    'Mens'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    105,
    105,
    24.50,
    'Sports'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    106,
    106,
    29.99,
    'Health and Fitness'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    107,
    107,
    28.50,
    'Motoring'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    108,
    108,
    35.65,
    'Health and Fitness'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    109,
    109,
    24.70,
    'Homes and Gardens'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    110,
    110,
    29.90,
    'Health and Fitness'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    111,
    111,
    27.70,
    'Science and Technology'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    112,
    112,
    22.55,
    'Homes and Gardens'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    113,
    113,
    30.99,
    'Mens'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    114,
    114,
    28.50,
    'Life Style'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    115,
    115,
    7.50,
    'Newspaper'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    116,
    116,
    7.50,
    'Newspaper'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    117,
    117,
    39.99,
    'Science and Technology'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    118,
    118,
    24.80,
    'Business'
  );
INSERT
INTO PRODUCT VALUES
  (
    SEQ_PRODUCT_NO.nextval,
    119,
    119,
    29.99,
    'Computer Mag'
  );